package com.xcn.jdbc;
 
import java.sql.*;
import java.util.*;

import net.sf.json.*;

import org.apache.log4j.*;

import com.xcn.lang.*;


/**
 * QueryBuilder
 * Mysql 쿼리 결과를 실행 
 *
 * 
 * @since 2011-10-13
 * @author jochangmin
 */
public class QueryBuilder
{

	private static Logger LOGGER = Logger.getLogger ( Logger.class );
	
	private static String driver = null;
	
	private static String url = null;
	
	private static String database = null;
	
	private static String userid = null;
	
	private static String userpw = null;

	public QueryBuilder( String driver, String url, String database, String userid, String userpw )
	{
		this.driver = driver;
		this.url = url;
		this.database = database;
		this.userid = userid;
		this.userpw = userpw;
	}
	
	
	public void queryMapping( String query, Map <String, String> item )
	{
		
	}
	
	/**
	 * 쿼리 insert, update 처리.
	 * @param query
	 * @param obj
	 * @return
	 * @throws Exception
	 */
	public int executeUpdate ( String query, String... obj ) throws Exception
	{
		int result = 0;
		Connection _con = null;
		PreparedStatement _ps = null;
		DBConnection _db = null;
		try
		{
			_con = _db.getConnection ( driver, url, database, userid, userpw );

			if ( _con != null )
			{
				_ps = _con.prepareStatement ( query );
				short idx = 1;
				for ( int i = 0 ; i < obj.length ; i++ )
				{
					if ( obj[i] != null ) _ps.setString ( idx++, String.valueOf ( obj[i] ) );
				}
				result = _ps.executeUpdate ( );
			}
		}
		catch ( Exception e )
		{
			LOGGER.error ( "Mysql getQueryResult Exception : " + e.getMessage ( ) );
			e.printStackTrace ( );
			throw new Exception ( e.getMessage ( ) );
		}
		finally
		{
			_db.close ( _con, null, _ps, null );
		}
		return result;
	}
	
	
	/**
	 * 쿼리 결과 리턴
	 * 
	 * @param query
	 * @throws Exception 
	 */
	public JSONObject getQueryResultObj ( String query, String... params ) throws Exception
	{
		JSONObject result = null;

		Connection _con = null;
		ResultSet _rs = null;
		PreparedStatement _ps = null;
		DBConnection _db = null;
		try
		{
			_con = _db.getConnection ( driver, url, database, userid, userpw );
			if ( _con != null )
			{
				_ps = _con.prepareStatement ( query );
				short idx = 1;
				for ( int i = 0 ; i < params.length ; i++ )
				{
					if ( params[i] != null ) _ps.setString ( idx++, params[i] );
				}
	
				_rs = _ps.executeQuery ( );
				
				ResultSetMetaData rsmd = _rs.getMetaData ( );
				int colCount = rsmd.getColumnCount ( );
				if( _rs.next ( ) )
				{
					result = new JSONObject ( );
					for ( int i = 1 ; i <= colCount ; i++ )
					{
						result.put ( rsmd.getColumnLabel ( i ), StringUtils.objFixNull ( _rs.getObject ( i ) ) );
					}
				}
			}
		}
		catch ( Exception e )
		{
			e.printStackTrace ( );
			throw new Exception ( e.getMessage ( ) );
		}
		finally
		{
			_db.close ( _con, _rs, _ps, null );
		}
		return result;
	}
	
	
	/**
	 * 쿼리 결과 리턴
	 * 
	 * @param query
	 * @throws Exception 
	 */
	public Map <String, String> getQueryResultItem ( String query, String... params ) throws Exception
	{
		Map <String, String> result = null;

		Connection _con = null;
		ResultSet _rs = null;
		PreparedStatement _ps = null;
		DBConnection _db = null;
		try
		{
			_con = _db.getConnection ( driver, url, database, userid, userpw );
			if ( _con != null )
			{
				_ps = _con.prepareStatement ( query );
				short idx = 1;
				for ( int i = 0 ; i < params.length ; i++ )
				{
					if ( params[i] != null ) _ps.setString ( idx++, params[i] );
				}
	
				_rs = _ps.executeQuery ( );
				
				ResultSetMetaData rsmd = _rs.getMetaData ( );
				int colCount = rsmd.getColumnCount ( );
				if( _rs.next ( ) )
				{
					result = new HashMap <String, String> ( );
					for ( int i = 1 ; i <= colCount ; i++ )
					{
						result.put ( rsmd.getColumnLabel ( i ), StringUtils.objFixNull ( _rs.getObject ( i ) ) );
					}
				}
			}
		}
		catch ( Exception e )
		{
			e.printStackTrace ( );
			throw new Exception ( e.getMessage ( ) );
		}
		finally
		{
			_db.close ( _con, _rs, _ps, null );
		}
		return result;
	}
	

	/**
	 * 쿼리 결과 리턴
	 * 
	 * @param query
	 * @throws Exception 
	 */
	public JSONArray getQueryResult ( String query, ArrayList <Map <String, String>> parameter ) throws Exception
	{
		JSONArray result = new JSONArray ( );

		Connection _con = null;
		ResultSet _rs = null;
		PreparedStatement _ps = null;
		DBConnection _db = null;
		try
		{
			_con = _db.getConnection ( driver, url, database, userid, userpw );

			if ( _con != null )
			{
				_ps = _con.prepareStatement ( query );
				for ( int i = 0 ; i < parameter.size ( ) ; i++ )
				{
					Map <String, String> _param = parameter.get ( i );
					String type = StringUtils.objFixNull ( _param.get ( "TYPE" ) );

					if ( type.equals ( "String" ) ) _ps.setString ( (i+1), StringUtils.objFixNull ( _param.get ( "VALUE" ) ) );
					else if ( type.equals ( "int" ) ) _ps.setInt( (i+1), StringUtils.objFixZero ( _param.get ( "VALUE" ) ) );
					else _ps.setString ( (i+1), StringUtils.objFixNull ( _param.get ( "VALUE" ) ) );
				}

				_rs = _ps.executeQuery ( );

				JSONObject item = null;
				ResultSetMetaData rsmd = _rs.getMetaData ( );
				int colCount = rsmd.getColumnCount ( );
				while ( _rs.next ( ) )
				{
					item = new JSONObject ( );
					for ( int i = 1 ; i <= colCount ; i++ )
					{
						item.put ( rsmd.getColumnLabel ( i ), StringUtils.objFixNull ( _rs.getObject ( i ) ) );
					}
					result.add ( item );
				}
			}
		}
		catch ( Exception e )
		{
			LOGGER.error ( "Mysql getQueryResult Exception : " + e.getMessage ( ) );
			e.printStackTrace ( );
			throw new Exception ( e.getMessage ( ) );
		}
		finally
		{
			_db.close ( _con, _rs, _ps, null );
		}

		return result;
	}

	
	/**
	 * 쿼리 결과 리턴
	 * 
	 * @param query
	 * @throws Exception 
	 */
	public ArrayList<Map<String, String>> getQueryResultList ( String query ) throws Exception
	{
		ArrayList <Map<String, String>> result = new ArrayList <Map<String, String>> ( );

		Connection _con = null;
		ResultSet _rs = null;
		PreparedStatement _ps = null;
		DBConnection _db = null;
		try
		{
			_con = _db.getConnection ( driver, url, database, userid, userpw );

			if ( _con != null )
			{
				_ps = _con.prepareStatement ( query );
				_rs = _ps.executeQuery ( );

				Map <String, String> item = null;
				ResultSetMetaData rsmd = _rs.getMetaData ( );
				int colCount = rsmd.getColumnCount ( );
				while ( _rs.next ( ) )
				{
					item = new HashMap <String, String> ( );
					for ( int i = 1 ; i <= colCount ; i++ )
					{
						item.put ( rsmd.getColumnLabel ( i ), StringUtils.objFixNull ( _rs.getObject ( i ) ) );
					}
					result.add ( item );
				}
			}
		}
		catch ( Exception e )
		{
			LOGGER.error ( "Mysql getQueryResultList Exception : " + e.getMessage ( ) );
			e.printStackTrace ( );
			throw new Exception ( e.getMessage ( ) );
		}
		finally
		{
			_db.close ( _con, _rs, _ps, null );
		}

		return result;
	}

	
	/**
	 * 쿼리 결과 리턴
	 * 
	 * @param query
	 * @throws Exception 
	 */
	public JSONArray getQueryResult ( String query, String... params ) throws Exception
	{
		JSONArray result = new JSONArray ( );

		Connection _con = null;
		ResultSet _rs = null;
		PreparedStatement _ps = null;
		DBConnection _db = null;
		try
		{
			_con = _db.getConnection ( driver, url, database, userid, userpw );

			if ( _con != null )
			{
				_ps = _con.prepareStatement ( query );
				short idx = 1;
				for ( int i = 0 ; i < params.length ; i++ )
				{
					if ( params[i] != null ) _ps.setString ( idx++, params[i] );
				}
				
				_rs = _ps.executeQuery ( );

				JSONObject item = null;
				ResultSetMetaData rsmd = _rs.getMetaData ( );
				int colCount = rsmd.getColumnCount ( );
				while ( _rs.next ( ) )
				{
					item = new JSONObject ( );
					for ( int i = 1 ; i <= colCount ; i++ )
					{
						item.put ( rsmd.getColumnLabel ( i ), StringUtils.objFixNull ( _rs.getObject ( i ) ) );
					}
					result.add ( item );
				}
			}
		}
		catch ( Exception e )
		{
			LOGGER.error ( "Mysql getQueryResult Exception : " + e.getMessage ( ) );
			e.printStackTrace ( );
			throw new Exception ( e.getMessage ( ) );
		}
		finally
		{
			_db.close ( _con, _rs, _ps, null );
		}

		return result;
	}
	
	/**
	 * ResultSet to JSONArray
	 * @param _rs
	 * @return
	 * @throws Exception 
	 */
	public static JSONArray resultSetToJsonArray( ResultSet _rs ) throws Exception
	{
		JSONArray result = new JSONArray ( );
		try
		{
			JSONObject item = null;
			ResultSetMetaData rsmd = _rs.getMetaData ( );
			int colCount = rsmd.getColumnCount ( );
			while ( _rs.next ( ) )
			{
				item = new JSONObject ( );
				for ( int i = 1 ; i <= colCount ; i++ )
				{
					item.put ( rsmd.getColumnLabel ( i ), StringUtils.objFixNull ( _rs.getObject ( i ) ) );
				}
				result.add ( item );
			}
		}
		catch ( Exception e )
		{
			LOGGER.error ( "Mysql resultSetToJsonArray Exception : " + e.getMessage ( ) );
			e.printStackTrace ( );
			throw new Exception ( e.getMessage ( ) );
		}
		finally
		{
			try{ if ( _rs != null ) _rs.close ( ); }catch (Exception e) {e.printStackTrace ( );}
		}
		
		return result;
	}
	
	/**
	 * ResultSet to JSONArray
	 * @param _rs
	 * @return
	 * @throws Exception 
	 */
	public static Map<String,String> resultSetToMap( ResultSet _rs ) throws Exception
	{
		Map<String,String> result = null;

		try
		{
			ResultSetMetaData rsmd = _rs.getMetaData ( );
			int colCount = rsmd.getColumnCount ( );
			if ( _rs.next ( ) )
			{
				result = new HashMap<String,String> ( );
				for ( int i = 1 ; i <= colCount ; i++ )
				{
					result.put ( rsmd.getColumnLabel ( i ), StringUtils.objFixNull ( _rs.getObject ( i ) ) );
				}
			}
		}
		catch ( Exception e )
		{
			LOGGER.error ( "Mysql resultSetToJsonArray Exception : " + e.getMessage ( ) );
			e.printStackTrace ( );
			throw new Exception ( e.getMessage ( ) );
		}
		finally
		{
			try{ if ( _rs != null ) _rs.close ( ); }catch (Exception e) {e.printStackTrace ( );}
		}
		
		return result;
	}
	
	public static String getDriver ( )
	{
		return driver;
	}


	public static void setDriver ( String driver )
	{
		QueryBuilder.driver = driver;
	}


	public static String getUrl ( )
	{
		return url;
	}


	public static void setUrl ( String url )
	{
		QueryBuilder.url = url;
	}


	public static String getDatabase ( )
	{
		return database;
	}


	public static void setDatabase ( String database )
	{
		QueryBuilder.database = database;
	}


	public static String getUserid ( )
	{
		return userid;
	}


	public static void setUserid ( String userid )
	{
		QueryBuilder.userid = userid;
	}


	public static String getUserpw ( )
	{
		return userpw;
	}


	public static void setUserpw ( String userpw )
	{
		QueryBuilder.userpw = userpw;
	}
	
}
